﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using YwangHRMSys.DBUtility;
using YwangHRMSys.Model;


namespace YwangHRMSys.DAL
    {
        public class CategoryDAL
            {




            /// <summary>
            /// 以倒序的形式排序所有数据
            /// </summary>
            /// <returns></returns>
                public CategoryModel[] ListAll()
                {
                    DataTable table = SqlHelper.ExecuteDataTable("SELECT id,Category ,Name FROM dbo.Category ORDER BY Category DESC");

                    
                return ToCategory(table);
                }

            /// <summary>
            /// 查询出类别有多少种，排除重复的存在
            /// </summary>
            /// <returns></returns>
                public IEnumerable<CategoryModel> ListCategory()
            {  
                    var list = new List <CategoryModel>();
                DataTable table = SqlHelper.ExecuteDataTable("select distinct Category from dbo.Category");

             
                foreach (DataRow row in table.Rows)
                    {

                    CategoryModel category = new CategoryModel();
                   category.Category = (string) row["Category"];
                    list.Add(category);
                    } 
                      
                    
                return list;


            }
            /// <summary>
            /// 根据指定Category查询出每一个Category中有多少数据
            /// </summary>
            /// <param name="category"></param>
            /// <returns></returns>
                public CategoryModel[] GetByCategory(string category)
                {
                    DataTable table = SqlHelper.ExecuteDataTable("SELECT id,Category ,Name FROM dbo.Category where Category=@Category", new SqlParameter("@Category", category));
                    return ToCategory(table);
                }

            /// <summary>
            /// 根据ID来查询该行数据
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
                public CategoryModel GetById(Guid id)
                {
                    DataTable table = SqlHelper.ExecuteDataTable("SELECT id,Category ,Name FROM dbo.Category where Id=@Id",new SqlParameter("@Id",id));
                    if (table.Rows.Count<=0)
                        {
                            return null;
                        }
                    if (table.Rows.Count==1)
                        {
                            return toModel(table.Rows[0]);
                        }
                    throw new Exception("存在ID重复");
                }

            /// <summary>
            /// 根据ID修改类别信息
            /// </summary>
            /// <param name="name"></param>
            /// <param name="id"></param>
                public void Update(CategoryModel category)
                {
                    SqlHelper.ExecuteNonQuery("UPDATE dbo.Category SET Name=@Name WHERE Id=@Id", new SqlParameter("@Name", category.Name), new SqlParameter("@Id", category.Id));
                }


            /// <summary>
            /// 添加类别信息
            /// </summary>
            /// <param name="category"></param>
                public void  Insert(CategoryModel category)
                {
                    SqlHelper.ExecuteNonQuery("INSERT INTO dbo.Category (  Name, Category )VALUES  ( @Name, @Category )", new SqlParameter("@Name", category.Name), new SqlParameter("@Category", category.Category));
                }

                private CategoryModel[] ToCategory(DataTable table)
                {
                    CategoryModel[] Category = new CategoryModel[table.Rows.Count];
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                       Category[i] = toModel(table.Rows[i]);
                    

                    }
                    return Category;
                }


                private CategoryModel toModel( DataRow row )
                {
                    CategoryModel category=new CategoryModel();
                    category.Id =(Guid) row["Id"];
                    category.Name = (string) row["Name"];
                    category.Category = (string)row["Category"];
                    return category;
                }

            }
    }